SQL server and dates :( - Xtreme Visual Basic Talk

07-27-2005, 11:14 AM

crabby

mostly gone

Retired Moderator
* Expert *

Join Date: Aug 2002

Location: London / UK

Posts: 2,721

SQL server and dates :(

Actually there is two questions that bug me.
1. that will be simple I'm apparantly can't too blind to make out a way to default a datetime column to the equivalent of NOW
2. thats more comestic but ...

Code:

SELECT t1.f1, t2.f1 AS 'blub', ....
FROM t1, t2
GROUP BY t1.f1, t2.f1

now I havent yet managed it to display t2.f1 in any other the then format the SQL server like best (2005-07-27 00:00:00) and thats no good .I've tried a CONVERT in the SELECT statement and in the GROUP statement but to no avail any help would be appreciated.

Mike

__________________
there are always two sides of a story and 12 ways to sing a song or write some code. so whats wrong ?

Avatar by lebb

07-27-2005, 11:22 AM

HardCode

Ultimate Contributor

Forum Leader
* Expert *

Join Date: Feb 2004

Location: New Jersey

Posts: 3,338

1. GetDate()
2. I have never found a solution to this either, so I always just format the date on the receiving end of the recordset when putting it out to a form control or report. Perhaps you can concatenate your own VARCHAR() using each part of the date/time field? For example, in pseudocode:

CAST(<month part> AS VARCHAR(2)) + "/" + CAST(<day part> AS VARCHAR(2)) + "/" + CAST(<year part> AS VARCHAR(4)) etc

I'll try to play around with this a bit and get back to you after lunch

__________________
DON'T CLICK HERE

Useful forum tags: [VB][/VB], [CODE][/CODE], [HTML][/HTML]

07-27-2005, 11:26 AM

crabby

mostly gone

Retired Moderator
* Expert *

Join Date: Aug 2002

Location: London / UK

Posts: 2,721

Thanks Joe, I'll give that a go but it's more then one date there (lucky me

)

__________________
there are always two sides of a story and 12 ways to sing a song or write some code. so whats wrong ?

Avatar by lebb

07-27-2005, 11:28 AM

HardCode

Ultimate Contributor

Forum Leader
* Expert *

Join Date: Feb 2004

Location: New Jersey

Posts: 3,338

Oh, I meant use the Date functions to extract the month, day, year

CAST(<month part from the propert Date function> AS VARCHAR(2)) + ...

I just haven't memorized them, and I am trying to keep the ziti off the keyboard

__________________
DON'T CLICK HERE

Useful forum tags: [VB][/VB], [CODE][/CODE], [HTML][/HTML]

07-27-2005, 11:35 AM

crabby

mostly gone

Retired Moderator
* Expert *

Join Date: Aug 2002

Location: London / UK

Posts: 2,721

Code:

SELECT [f1] as 'F0',CAST([f5] AS varchar(11)),... 
FROM [ForeCast].[dbo].[t_test]
GROUP BY [F1],[f5],...

returns 'Jul 27 2005'

we are getting there

__________________
there are always two sides of a story and 12 ways to sing a song or write some code. so whats wrong ?

Avatar by lebb

07-27-2005, 11:36 AM

crabby

mostly gone

Retired Moderator
* Expert *

Join Date: Aug 2002

Location: London / UK

Posts: 2,721

Quote:

Originally Posted by HardCode

Oh, I meant use the Date functions to extract the month, day, year

CAST(<month part from the propert Date function> AS VARCHAR(2)) + ...

I just haven't memorized them, and I am trying to keep the ziti off the keyboard

Thank god I just to write it once

__________________
there are always two sides of a story and 12 ways to sing a song or write some code. so whats wrong ?

Avatar by lebb

07-27-2005, 11:44 AM

crabby

mostly gone

Retired Moderator
* Expert *

Join Date: Aug 2002

Location: London / UK

Posts: 2,721

Now there we go

Code:

SELECT [f1] as 'F0',CONVERT(varchar(8),[f5],103), SUM((([f2]+[f3])/2)*[f4]) as AVE 
FROM [ForeCast].[dbo].[t_test]
GROUP BY [F1],[f5]

The 103 is a style you find under CONVERT in MSDN even though its not so nice to read.

__________________
there are always two sides of a story and 12 ways to sing a song or write some code. so whats wrong ?

Avatar by lebb

07-27-2005, 11:50 AM

reboot

Keeper of foo

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Graceland

Posts: 15,612

Try CONVERT(varchar(10), getdate(), 101)

Edit by reboot: oops... too slow

__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb