SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Vince McMahon: Dec 18, 2003

I want to store some data like this:

code:

parent1-child-child-child
       -child-child
       -child-child-child-child
       -child
parent2-child-child
       -child
etc

I want mySQL to pull it out in the right order, so I can then hide most of it with javascript and then make it expandable. I'm having trouble thinking up how to store the data though. My first thought was have an "id" column and a "parent_id" column, so the first line of children would have their parent_id column set to the parent's id, the second tier of children would have their parent_id as the corresponding first tier child id, and so forth.

The problem with this is that I can't figure out how to pull out the information again without joining the table to itself for each layer of children, which means I'd have to limit the number of possible tiers... I guess that wouldn't be the end of the world but it feels messy. Anyone know a better way?

# ? Feb 16, 2010 20:38

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 23:26

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Hierarchical data retrieval is not one of the strong points of a relational database. It's a longstanding problem that everyone has. There is no easy solution that will provide super duper easy access to the data.

# ? Feb 16, 2010 20:42

butch thumperton: Jun 30, 2003

Vince McMahon posted:

I want to store some hierarchical data

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

# ? Feb 16, 2010 20:42

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Is there any variable/size significance to Dec 31 2069 (2069-12-31 00:00:00.000)? I'm getting some weird imports from other systems that are using this date and I need to figure out if it's happening because of bad data types on their side or bad conversion on mine. Most of the time appropriate dates show up in my system (datetime fields) but occasionally a 2069 will sneak in there, when the data from the originating system when I query it is 'clean'.

EDIT-never mind, found. Largest possible 2-digit datetime value (here we come y2k70!). I knew it seemed familiar. I think he's getting cute with data types on the originating system's end.

Scaramouche fucked around with this message at 22:03 on Feb 16, 2010

# ? Feb 16, 2010 22:00

Namlemez: Jul 9, 2003

Triple Tech posted:

Hierarchical data retrieval is not one of the strong points of a relational database. It's a longstanding problem that everyone has. There is no easy solution that will provide super duper easy access to the data.

I'd say that's more MySQL. I've had to do hierarchical data in Oracle and used CONNECT BY. PostgreSQL also added WITH RECURSIVE that lets you do similar things, and I think that one is somewhat ANSI-standard.

http://www.postgresql.org/docs/8.4/static/queries-with.html

# ? Feb 17, 2010 02:20

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Here's a comparison of four popular ways to model hierarchical data.

Bad Titty Puker fucked around with this message at 06:37 on Feb 17, 2010

# ? Feb 17, 2010 06:34

indulgenthipster: Mar 16, 2004; Make that a pour over

I'm trying to do a multi-table delete with MySQL, here is my current SQL statement

code:

DELETE FROM
papers,authors,keywords
	WHERE
	papers.papid = 55
	AND authors.papid = 55
	AND keywords.papid = 55

Not sure what is wrong with it, I will also have to add additional tables to delete from as well, is there a better way to go about this?

# ? Feb 17, 2010 23:50

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

VerySolidSnake posted:

Is there a better way to go about this?

Just write separate delete statements. Multi-table delete is sort of a crazy construction, and doesn't really buy you anything. Why do you think you need it?

Anyway, to directly answer your question, I think you need to have delete t1,t2 from t1,t2... as your syntax, based on your query I strongly suspect you should be using full outer joins instead of inner joins (on a second glance, you probably only need left/right outer join in code you have, but that assumes you have foreign keys setup right and don't have any dangling references), and it looks like you may have foreign keys in your table structure which will randomly cause that statement to fail because MySQL doesn't do the underlying deletes in any particular order.

ShoulderDaemon fucked around with this message at 01:00 on Feb 18, 2010

# ? Feb 18, 2010 00:55

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

VerySolidSnake posted:

I'm trying to do a multi-table delete with MySQL, here is my current SQL statement
code:
DELETE FROM
papers,authors,keywords
	WHERE
	papers.papid = 55
	AND authors.papid = 55
	AND keywords.papid = 55
Not sure what is wrong with it, I will also have to add additional tables to delete from as well, is there a better way to go about this?

i'm going to assume papers it the main table?

code:

Delete from papers,authors,keywords
USING papers
Inner join Authors
Inner Join Keywords
where
papers.papid = 55
AND authors.papid= papers.papid 
AND keywords.papid = papers.papid

# ? Feb 18, 2010 00:57

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

Sprawl posted:

i'm going to assume papers it the main table?

code:

Delete from papers,authors,keywords
USING papers
Inner join Authors
Inner Join Keywords
where
papers.papid = 55
AND authors.papid= papers.papid 
AND keywords.papid = papers.papid

Note that this will delete zero rows if, for example, a paper has no keywords (because the inner join will find no rows at all). You can convert it to left outer joins in this case, but then it will fail if you are trying to delete keywords for a paper that no longer exists. You can prevent that degenerate case by creating foreign keys to prevent dangling references, but then it will randomly fail because multi-table delete doesn't delete from the tables in any particular order, so it may choose to delete frm the papers table first, break the foreign keys, and abort the transaction.

# ? Feb 18, 2010 01:02

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

ShoulderDaemon posted:

Note that this will delete zero rows if, for example, a paper has no keywords (because the inner join will find no rows at all). You can convert it to left outer joins in this case, but then it will fail if you are trying to delete keywords for a paper that no longer exists. You can prevent that degenerate case by creating foreign keys to prevent dangling references, but then it will randomly fail because multi-table delete doesn't delete from the tables in any particular order, so it may choose to delete frm the papers table first, break the foreign keys, and abort the transaction.

Yes there are a few issues with it i normally would never use such a method and stick to a transaction with 1 table at a time myself so you can rollback if something like an error happens.

# ? Feb 18, 2010 01:06

Primpod: Dec 25, 2007; jamming on crusty white

Hi, I've never posted in here before, I've worked with Sql for just under a year now.

Today, what I effectively had to do was select from a single table of around two thousand records two rows, selecting them by the guid primary key of the table. The table has a sort order, so one row has priority over the other. If any of the values in the first row are null, use the value in the second row, if not, the value in the second row is irrelevant. The end goal is to return one row.

The purpose of this is to use it in an update statement on the same table, using it to update the first row to the returned row. I eventually hacked my way to solving it using select cases and subselects, but it was really poorly done, and it really felt like the kind of thing that in the hands of someone with a more organised mind would be fairly easy and quicker.

Does anyone have any suggestions?

# ? Feb 18, 2010 01:31

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Primpod posted:

Hi, I've never posted in here before, I've worked with Sql for just under a year now.

Today, what I effectively had to do was select from a single table of around two thousand records two rows, selecting them by the guid primary key of the table. The table has a sort order, so one row has priority over the other. If any of the values in the first row are null, use the value in the second row, if not, the value in the second row is irrelevant. The end goal is to return one row.

The purpose of this is to use it in an update statement on the same table, using it to update the first row to the returned row. I eventually hacked my way to solving it using select cases and subselects, but it was really poorly done, and it really felt like the kind of thing that in the hands of someone with a more organised mind would be fairly easy and quicker.

Does anyone have any suggestions?

Well i assume they have a common data point right?

code:

mysql 5.1.x

CREATE TABLE `test` (
  `product` varchar(13) DEFAULT NULL,
  `account` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


INSERT INTO `test` (`product`,`account`)
VALUES
	('test1',NULL),
	('test1',1),
	('test2',NULL),
	('test2',2);

select * from test 
where 
not isNull(product) 
and not isNull(account)

If this can't work for you maybe you could describe your data or what sql engine your working with etc etc a little more.

Sprawl fucked around with this message at 02:27 on Feb 18, 2010

# ? Feb 18, 2010 02:17

indulgenthipster: Mar 16, 2004; Make that a pour over

Thanks for the help!

# ? Feb 18, 2010 14:23

Corsair Jr: Sep 10, 2009

So I've got multiple tables and 2 have values I'm trying to sum. I've been trying to figure out how to get this in one query for about 4 hours and I'm only going in circles.

A bunch of tables are involved, and its too difficult to explain completely, but essentially what I'm running into is double/triple/etc counting for records that appear in certain tables that many times.

The tables look like so:

code:

programs
Program_ID   Program_Name
1            Basketball
2            Baseball
3            Hockey

values1
Program_ID   Amount1
1            $10  
2            $20

values2
Program_ID   Amount2
2            $20  
2            $20  
3            $10

Its possible to have multiple amounts for each program in either table, or no amounts at all. 

The query I have looks something like this:

SELECT   program_id,
         program_name,
         Sum(amount1) + sum(amount2) 
FROM     programs AS p 
         LEFT JOIN values1 
           ON values1.program_id = p.program_id 
         LEFT JOIN values2 
           ON values2.program_id = p.program_id 
GROUP BY program_id, program_name

The problem is that this query gives me the wrong amounts. I want the sum to reflect only the total for each program in each table, but my query over values Program_ID 2 (baseball). That program should show $60 (the sum of all values for that program_id from both values tables).

Corsair Jr fucked around with this message at 01:28 on Feb 23, 2010

# ? Feb 23, 2010 01:24

slartibartfast: Nov 13, 2002

Corsair Jr posted:

code:
programs
Program_ID   Program_Name
1            Basketball
2            Baseball
3            Hockey

values1
Program_ID   Amount
1            $10  
2            $20

values2
Program_ID   Amount
2            $20  
2            $20  
3            $10
The problem is that this query gives me the wrong amounts. I want the sum to reflect only the total for each program in each table, but my query over values Program_ID 2 (baseball). That program should show $60 (the sum of all values for that program_id from both values tables).

Are the columns the same names/datatypes in values1 and values2?

If so, you can UNION those tables together, and then join that back onto your programs table.

# ? Feb 23, 2010 01:29

Corsair Jr: Sep 10, 2009

slartibartfast posted:

Are the columns the same names/datatypes in values1 and values2?

If so, you can UNION those tables together, and then join that back onto your programs table.

Actually no. Different names, same data types (money). I tried a union, but it was giving me multiples like so:

code:

Program_ID   Program_Name   Total_Value
1            Basketball     $10 
2            Baseball       $20
2            Baseball       $40
3            Hockey         $10

What I want is just one record for baseball showing $60. Do I need to adjust my group by?

Again, I'm grossly oversimplifying this. The code has a shitload of conditional statements, aggregates, and intersection tables that are make it look messy as hell.

[edit]Actually I'm a moron and I think just grouping at the end of the UNION will fix the sums. Thanks guys.

Corsair Jr fucked around with this message at 01:45 on Feb 23, 2010

# ? Feb 23, 2010 01:35

Fly: Nov 3, 2002; moral compass

Vince McMahon posted:

I want to store some data like this:
code:
parent1-child-child-child
       -child-child
       -child-child-child-child
       -child
parent2-child-child
       -child
etc
I want mySQL to pull it out in the right order, so I can then hide most of it with javascript and then make it expandable. I'm having trouble thinking up how to store the data though. My first thought was have an "id" column and a "parent_id" column, so the first line of children would have their parent_id column set to the parent's id, the second tier of children would have their parent_id as the corresponding first tier child id, and so forth.

The problem with this is that I can't figure out how to pull out the information again without joining the table to itself for each layer of children, which means I'd have to limit the number of possible tiers... I guess that wouldn't be the end of the world but it feels messy. Anyone know a better way?

Read up on the modified preorder tree storage method in the link below. It's very useful and easy for storing and retrieving hierarchical data. It works beautifully for huge datasets, too.

butch thumperton posted:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

# ? Feb 23, 2010 04:06

Sub Par: Jul 18, 2001; Dinosaur Gum

I have a table like this:

code:

Person_ID    Transaction_Code
1            AA
1            BB
1            CC
2            AA
2            JJ
3            AA

I need to group it so that it appears like this:

code:

Person_ID    All_Transaction_Codes
1            AA, BB, CC
2            AA, JJ
3            AA

I'm having a brain freeze and can't think of a particularly easy way to do this (Oracle 10g). Can someone help? Thanks.

# ? Feb 23, 2010 17:23

var1ety: Jul 26, 2004

Sub Par posted:

I'm having a brain freeze and can't think of a particularly easy way to do this (Oracle 10g). Can someone help? Thanks.

It's a pain in 10g. An article linked earlier in the thread does the best job of summarizing the task in Oracle environments. You will find that many of the solutions do not speak to the related problem of ordering, however.

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

I do this infrequently and use specific functions to solve the problem.

# ? Feb 23, 2010 17:41

Sub Par: Jul 18, 2001; Dinosaur Gum

var1ety posted:

It's a pain in 10g. An article linked earlier in the thread does the best job of summarizing the task in Oracle environments. You will find that many of the solutions do not speak to the related problem of ordering, however.

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

I do this infrequently and use specific functions to solve the problem.

Thanks. I may end up doing this outside the database, as I can think of a million ways to that. I'll check out that link.

Edit: my vendor is waiting for 11g release 2 to come out on for Windows before upgrading. Anyone in the know have an idea when that might be?

Edit again: WM_CONCAT() to the rescue! Thanks again, that worked like a charm.

Sub Par fucked around with this message at 17:47 on Feb 23, 2010

# ? Feb 23, 2010 17:42

Tinestram: Jan 13, 2006; Excalibur? More like "Needle"; Grimey Drawer

I'm trying to use phpMyAdmin with a database user that doesn't have SHOW DATABASES permissions. As a result, I can't select one. I've tried using "use <database name>", which doesn't return any errors, but I still can't manipulate the database through the interface, other than using the SQL window. Is there any way I can configure phpMyAdmin to use a specific database? Or another tool I should use instead?

edit: resolved (kinda). Ended up using SQLyog instead. Not an ideal solution, since it's a Windows install, but it got the job done.

Tinestram fucked around with this message at 10:44 on Feb 27, 2010

# ? Feb 26, 2010 23:51

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a backup script that looks like this:

code:

pg_dump -U doop mydb --table=mytable1 | bzip2 > /path/table1.sql.bz2
pg_dump -U doop mydb --table=mytable2 | bzip2 > /path/table2.sql.bz2
pg_dump -U doop mydb --table=mytable3 | bzip2 > /path/table3.sql.bz2

The only problem is that if someone were to add a record to table 3 after I started the backup script (which takes about 30 seconds to complete all tables), it won't work when trying to restore the backups, I'll get this error:

code:

ERROR:  insert or update on table "table3" violates foreign key constraint "somecolumn_id_refs_id_4932d05d"
DETAIL:  Key (somecolumn_id)=(322961) is not present in table "table1".

This is all done in a transaction, so since that one record is not present, the whole backup restore process is aborted. The restore script looks like this:

code:

bunzip2 -c /path/table1.sql.bz2 | psql table1
bunzip2 -c /path/table2.sql.bz2 | psql table2
bunzip2 -c /path/table3.sql.bz2 | psql table3

Is there any way I can tell postgres to ignore records that do not match up and just throw those ones out?

# ? Mar 5, 2010 20:48

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

You can pass multiple --table arguments to pg_dump, so your backups won't be inconsistent.

# ? Mar 5, 2010 22:43

nbv4: Aug 21, 2002; by Duchess Gummybuns

ShoulderDaemon posted:

You can pass multiple --table arguments to pg_dump, so your backups won't be inconsistent.

Ok, now I have this:

code:

pg_dump -U django logbook --format=c --compress=5
--table='(tagging|profile|foo|terds|derp|doop)_*' > dump.sql

which dumps it all to one file, which is nice, but the only problem is that my tables are very sensitive to the order that they are restored in (each table has a bunch of joins). Currently it works fine if I do this:

code:

pg_restore dump.sql --clean --table=table3 --format=c -a -d mydb;
pg_restore dump.sql --clean --table=table2 --format=c -a -d mydb;
pg_restore dump.sql --clean --table=table1 --format=c -a -d mydb;

but I'd rather have it in one single command, so I can take advantage of the -j switch. Apparently there is a -l switch that spits out a table of contents, which I can then edit the order of and then pass back in with -L, but the documentation on the postgres site is very limited on how this can be done. The TOC that gets spit out in my case is quite a bit different than the one in the docs... here is what mine looks like: http://pastebin.com/0Fj4GA3R How should I be ordering this stuff so it works?

# ? Mar 6, 2010 02:26

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

You can combine your pg_restore commands into a single command as well, which you should do to preserve integrity by keeping everything in a single transaction.

If you pass --disable-triggers to pg_restore then it will prevent foreign key checks from being applied to the data as it restores (or, rather, defer them to the end of the transaction) and allow your data to be restored in any order.

# ? Mar 6, 2010 04:05

Fruit Smoothies: Mar 28, 2004; The bat with a ZING

Easy question: If I have a query that returns some rows thus:

code:

-------------------------
| ID	| F1	| F2	|
-------------------------
| 1	| a	| b	|
-------------------------
| 2	| a	| c	|
-------------------------

Can I write a query so that `F1` and `F2` are passed to a secondary query that searches another table

code:

-------------------------
| F1	| F2	| F3	|
-------------------------
| a	| b	| z	|
-------------------------
| a	| c	| d	|
-------------------------

The idea is to go from the records of F1,F2 pairings to F3 values.

Is this possible in MySQL (innoDB) or do I need to use PHP to do the looping?

Sorry if this is trivial, but I've always just assumed I needed to loop before.

EDIT: I know I can do this with a JOIN, the trouble is, the F3 is then looked up in another table.

EDIT: 2... I now have the finished query using JOINS. If there's a way of doing it without JOINs, I'd be happy to know. Thanks.

Fruit Smoothies fucked around with this message at 02:22 on Mar 7, 2010

# ? Mar 7, 2010 01:20

nbv4: Aug 21, 2002; by Duchess Gummybuns

ShoulderDaemon posted:

If you pass --disable-triggers to pg_restore then it will prevent foreign key checks from being applied to the data as it restores

ah thanks that did it

# ? Mar 7, 2010 02:10

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Why are you avoiding joins?

# ? Mar 7, 2010 04:51

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Fruit Smoothies posted:

Is this possible in MySQL (innoDB) or do I need to use PHP to do the looping?

edit: search for "pivot table" or "crosstab" and MySQL

Bad Titty Puker fucked around with this message at 05:58 on Mar 7, 2010

# ? Mar 7, 2010 05:12

Fruit Smoothies: Mar 28, 2004; The bat with a ZING

Triple Tech posted:

Why are you avoiding joins?

I wasn't avoiding them, I forgot I could use them.

# ? Mar 7, 2010 16:47

Saltin: Aug 20, 2003; Don't touch

Could someone give me some guidance on a small problem I am having?

I have a field in a table which is a timestamp MM/DD/YYYY HH:MM:SS (then AM or PM).

I have a view which I need to include this field in, but for my purposes it would be far better to have the field shown simply as MM/DD/YYYY. Seems simple enough but the strings can look like 7/10/2006 or 12/10/2009, so I am unable to simply count from the LEFT reliably as the month field doesnt use leading zeros and the day can be single or double digits. I can't count from the RIGHT because it's a 12 hour clock.

I do have one constant, which is the space after MM/DD/YYYY_HH:MM:SS (_ represents the space here).

Is there a way I can build it into the view to show what I need?

# ? Mar 8, 2010 19:56

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Not sure of your platform but I use DATEPART on MSSQL to pull out specific date sub-elements when I can't be arsed to CONVERT and so on. It seems to work most of the time; however the core problem is likely that your date/time inputs are inconsistent and not reliable, despite whatever magic massaging the SQL server is doing on the column. Here's some documentation:

http://www.w3schools.com/SQl/func_datepart.asp

# ? Mar 8, 2010 20:02

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Saltin posted:

Could someone give me some guidance on a small problem I am having?

I have a field in a table which is a timestamp MM/DD/YYYY HH:MM:SS (then AM or PM).

I have a view which I need to include this field in, but for my purposes it would be far better to have the field shown simply as MM/DD/YYYY. Seems simple enough but the strings can look like 7/10/2006 or 12/10/2009, so I am unable to simply count from the LEFT reliably as the month field doesnt use leading zeros and the day can be single or double digits. I can't count from the RIGHT because it's a 12 hour clock.

I do have one constant, which is the space after MM/DD/YYYY_HH:MM:SS (_ represents the space here).

Is there a way I can build it into the view to show what I need?

Is it stored as a DateTime column value or string.

Its very easy if its in there as a datetime.

Also what SQL are you using ( MySQL i think )

# ? Mar 8, 2010 20:09

Saltin: Aug 20, 2003; Don't touch

This is MSSQL, and the field is type datetime, sorry I should have mentioned that! Thanks for helping out.

# ? Mar 8, 2010 20:12

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Saltin posted:

This is MSSQL, and the field is type datetime, sorry I should have mentioned that! Thanks for helping out.

OKay well that's really quite easy then

SELECT CONVERT(char(10),[MyDateField],101) as MyDateField

So This is a pretty silly process because they dont have a real date function but it gets the job done.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

if you want some other formats.

Sprawl fucked around with this message at 20:36 on Mar 8, 2010

# ? Mar 8, 2010 20:26

Saltin: Aug 20, 2003; Don't touch

Sprawl posted:

OKay well that's really quite easy then

Works a treat Sprawl, thanks alot. I see what you did there, always makes sense to me when I see it, can never think of it myself. I'm a SQL noob incase you can't tell : )

EDIT: Spoke to soon. It didnt dawn on me that you did this by converting... the output needs to remain datetime!

Saltin fucked around with this message at 21:31 on Mar 8, 2010

# ? Mar 8, 2010 20:46

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

Saltin posted:

Works a treat Sprawl, thanks alot. I see what you did there, always makes sense to me when I see it, can never think of it myself. I'm a SQL noob incase you can't tell : )

EDIT: Spoke to soon. It didnt dawn on me that you did this by converting... the output needs to remain datetime!

Could you do a convert inside a convert? If so, try converting that char convert as a datetime. I'm sure there's an easier way but I don't know MSSQL.

# ? Mar 8, 2010 21:46

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Saltin posted:

Works a treat Sprawl, thanks alot. I see what you did there, always makes sense to me when I see it, can never think of it myself. I'm a SQL noob incase you can't tell : )

EDIT: Spoke to soon. It didnt dawn on me that you did this by converting... the output needs to remain datetime!

Oh change the Char(10) to datetime.

# ? Mar 8, 2010 21:49

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 23:26

slartibartfast: Nov 13, 2002

This is an inelegant solution (it's fuckin' retarted) but if all you need to do is strip off the time portion of a datetime field, you can use this:

code:

declare @mydate as datetime 
set @mydate = '2010-03-01 13:02:03.045'

select dateadd("d",0,DATEDIFF(day, 0, @mydate)) as cleanDate

And it'll return this:

code:

2010-03-01 00:00:00.000

That dateadd() voodoo is all you need. The rest of it is just proof-of-concept so you can paste it into SSMS and see it run.

Note that this still always returns the '00:00:00.000' poo poo at the end. You said the output needs to remain as datetime though, so that's what you're stuck with. The datetime data type does not support just mm/dd/yyyy without the hh:mm:ss:xxx. Just the way it goes in the wacky world of SQL Server 2000/2005.

Thank god SQL 2008 finally supports date and time as separate data types.

# ? Mar 8, 2010 22:23

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »