Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-12-21 : 08:05:07
|
| Sharon writes "1) Need today's date so use function GETDATE().2) SQL Server only has datetime fields (no date-only fields) so that is returned as 2006-12-20 10:49:01.7833) My software writes all dates as date + midnight i.e. 2006-12-20 00:00:00.0004) If the GETDATE() result is used as-is in a WHERE clause as the Start Date, the system doesn't pick up any records dated 20 Dec. That is because the are actually "dated" *before* the indicated start date/time.5) I can convert GETDATE() to char(10) format 103 to get 20/12/2006 and I can use that character value as my start date in SQL. SQL's implicit conversion recognises that as a date and will assume it to be 2006-12-20 00:00:00.000 *in processing the statement in Query Analyzer*.6) HOWEVER: SQL's implicit conversion, when the statement is run as part of xp_cmdshell (either as isql or osql), is reading the character 20/12/2006 as mm/dd/yyyy and (in this particular example) returning an error.So, Query Analyzer recognises that aa/bb/cccc is to be viewed as dd/mm/yyyy. But, xp_cmdshell views that as mm/dd/yyyy. Where is the underlying U.S. date format default that xp_cmdshell reads but QA doesn't - and how do I change it?Thanks in advance, Sharon." |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-21 : 08:10:55
|
| SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)Peter LarssonHelsingborg, Sweden |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-21 : 08:12:49
|
| Use Where datecol=DateAdd(day,Datediff(day,0,Getdate()),0) -- To get today's dataWhere datecol=DateAdd(day,Datediff(day,0,Getdate()),-1) --To get yesterday's dataDont use format function. It is just omitting the Time part from GETDATE()More on dateswww.sql-server-performance.com/fk_datetime.aspMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-21 : 08:54:47
|
| Post your code you are using this far here and we will revise it.Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
date format default in xp_cmdshell vs Query Analyzer