Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > Database and Reporting > SQL server and dates :(


Reply
 
Thread Tools Display Modes
  #1  
Old 07-27-2005, 11:14 AM
crabby's Avatar
crabby crabby is offline
mostly gone

Retired Moderator
* Expert *
 
Join Date: Aug 2002
Location: Oppum / Rhine area
Posts: 2,721
Default 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
Reply With Quote
  #2  
Old 07-27-2005, 11:22 AM
HardCode's Avatar
HardCode HardCode is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Feb 2004
Location: New Jersey
Posts: 3,338
Default

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]
Reply With Quote
  #3  
Old 07-27-2005, 11:26 AM
crabby's Avatar
crabby crabby is offline
mostly gone

Retired Moderator
* Expert *
 
Join Date: Aug 2002
Location: Oppum / Rhine area
Posts: 2,721
Default

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
Reply With Quote
  #4  
Old 07-27-2005, 11:28 AM
HardCode's Avatar
HardCode HardCode is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Feb 2004
Location: New Jersey
Posts: 3,338
Default

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]
Reply With Quote
  #5  
Old 07-27-2005, 11:35 AM
crabby's Avatar
crabby crabby is offline
mostly gone

Retired Moderator
* Expert *
 
Join Date: Aug 2002
Location: Oppum / Rhine area
Posts: 2,721
Default

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
Reply With Quote
  #6  
Old 07-27-2005, 11:36 AM
crabby's Avatar
crabby crabby is offline
mostly gone

Retired Moderator
* Expert *
 
Join Date: Aug 2002
Location: Oppum / Rhine area
Posts: 2,721
Default

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
Reply With Quote
  #7  
Old 07-27-2005, 11:44 AM
crabby's Avatar
crabby crabby is offline
mostly gone

Retired Moderator
* Expert *
 
Join Date: Aug 2002
Location: Oppum / Rhine area
Posts: 2,721
Default

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
Reply With Quote
  #8  
Old 07-27-2005, 11:50 AM
reboot's Avatar
reboot reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,612
Default

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

Edit by reboot: oops... too slow
__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->