January 2, 2005 at 4:22 pm
can anyone please tell how to format datetime of the output in select statement to show just the date and not time
date output is 2005-01-28 15:00:00
I want just the date 2005-01-28 and not the above
Thanks a lot
January 2, 2005 at 11:21 pm
Using getdate() as an example date:
select cast(floor(cast(getdate() as float)) as datetime)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 3, 2005 at 11:34 pm
Try this
CONVERT(Varchar(12),GetDate(),101)
Don't forget to change date type for other countries
REGARDS
Don
January 4, 2005 at 12:23 am
Hi,
try this -
CONVERT(VARCHAR, YourDateField, 111)
HTH.
January 4, 2005 at 2:13 am
I wrote the following for internal use. It just puts all the date format material from the books online together.
Joachim.
convert (varchar, field ,105)
Example: DD-MM-YYYY
select convert (varchar, a1,105) from a
insert into a select convert (datetime, '30-1-2000' , 105)
Example: DD-MM-YYYY
insert into a convert (datetime, '30-1-2000' , 105)
|
Without century (yy) |
With century (yyyy) |
Standard |
Input/Output** |
|
- |
0 or 100 (*) |
Default |
mon dd yyyy hh:miAM (or PM) |
|
1 |
101 |
USA |
mm/dd/yy |
|
2 |
102 |
ANSI |
yy.mm.dd |
|
3 |
103 |
British/French |
dd/mm/yy |
|
4 |
104 |
German |
dd.mm.yy |
|
5 |
105 |
Italian |
dd-mm-yy |
|
6 |
106 |
- |
dd mon yy |
|
7 |
107 |
- |
Mon dd, yy |
|
8 |
108 |
- |
hh:mm:ss |
|
- |
9 or 109 (*) |
Default + milliseconds |
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
|
10 |
110 |
USA |
mm-dd-yy |
|
11 |
111 |
JAPAN |
yy/mm/dd |
|
12 |
112 |
ISO |
yymmdd |
|
- |
13 or 113 (*) |
Europe default + milliseconds |
dd mon yyyy hh:mm:ss:mmm(24h) |
|
14 |
114 |
- |
hh:mi:ss:mmm(24h) |
|
- |
20 or 120 (*) |
ODBC canonical |
yyyy-mm-dd hh:mi:ss(24h) |
|
- |
21 or 121 (*) |
ODBC canonical (with milliseconds) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
|
- |
126(***) |
ISO8601 |
yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
|
- |
130* |
Hijri**** |
dd mon yyyy hh:mi:ss:mmmAM |
|
- |
131* |
Hijri**** |
dd/mm/yy hh:mi:ss:mmmAM |
Look at the next chapter, "MS SQL Server (Using date functions)" if the preferred format is not here.
SQLServer Books Online
Convert is limited to the above styles.
For a select there is an alternative in the function datepart
A datefunction gives a number for part of the date
str ( float_expression [ , length [ , decimal ] ] ) turns a number into a string
replace(string1,' ','0') turns the space in one character months and days into a 0
Example:
YYYY-MM-DD
select replace(str(year(a1),4) + '-' +str(month(a1),2) + '-' + str(day(a1),2),' ','0') from a
|
Function |
description |
|
Day(datefield) |
day of month |
|
month(datefield) |
month as number |
|
year(datefield) |
year as number |
|
datepart(format,datefield) |
part of date as number |
|
datename(format,datefield) |
part of date as string |
Datepart formats:
|
Datepart |
Abbreviations |
|
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
Datename formats:
|
Datepart |
Abbreviations |
|
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
The main difference between datepart and datename is that month becomes the name of the month in datename
SQLServer Books Online
January 4, 2005 at 3:49 pm
Hi,
Good discussion. Now, what is everyone's opinion on how it is better to compare dates, for exaple to select all records for today? There are 2 ways to do that, first by comparing strings and the second one by comparing 3 values. There is a third one too when you say that a date is more then yesterday and less then tomorrow, I use it sometimes too.
So what is more dependable?
1. Convert to string both the DateTime field value and GetDate() as was discussed in the previous posts using Convert with formats and compare those strings
2. I usually use the following:
Where Day(Mydate) = Day(GetDate())
AND Month(Mydate) = Month(GetDate())
AND Year(Mydate) = Year(GetDate())
Regards,Yelena Varsha
January 4, 2005 at 4:07 pm
1) WHERE (DATEDIFF(d, datelogged, GETDATE()) = 1)
2) WHERE (despatchdate BETWEEN '" & convDate(dteFromDate) & "' AND '" & convDate(dteToDate) & "')
'Using function to massage date.
3) WHERE orderdate > Getdate() - 3
January 5, 2005 at 9:53 am
Dgrover,
I do sometimes use your #3 Getdate() - 3
what about #2? the ConvDate function? it is not SQL function. I think it is Unix or something unless you have written your own and want to share with us.
I also found a good ASP VBScript function named ConvDate in the article
ASP Date/Time Formatting
http://www.pbdr.com/vbtips/asp/dateformat.htm
They say we may copy and paste this script for our own use.
Yelena
Regards,Yelena Varsha
January 5, 2005 at 2:38 pm
This is the convDate function I use it when a user is typing in a date .
<%
'This function recieves a date from text string in format dd/mm/yy or dd/mm/ccyy
'And creates a string that is compatible with inserting into sql as a datetime field
'If an empty string is passed it just passes back trimmed original
'Write Value Test value to sql database 'datetime' field
'Added 16/04/2003
'If a 2 digit year is passed then 20 is prepended onto year to build a CCYY year
'---------
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'---------
'pass a date as dd/mm/yy
Function convDate(theDate)
Dim Itemp
If TRIM(theDate) <> "" Then
sTemp = cdate(theDate)
dteArray = Split(sTemp,"/",-1,1)
If LenB(dteArray(2)) = 2 Then
dteArray(2) = "20" & dteArray(2)
End If
convDate =dteArray(2) & "/" & dteArray(1) & "/" & dteArray(0)
Else
convDate = Trim(theDate)
End If
End Function
%>
January 7, 2005 at 8:52 am
dgrover,
This is a good function. I will save it for the conversion from string to date, very helpful.
I probably have just to create a user-defined function for stripping time out of datetime field in SQL, using any of the techniques discussed above.
Thanks.
Yelena
Regards,Yelena Varsha
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply