Formatting Time

[imtheodore]

I have a field with the date/time in it, in the format:

yyyy-nn-dd hh:mm:ss:000

and example would be:

2009-1-10 23:12:00:000

Is there any way for me to just list the hour in 24 hour format?
For the above example I would need 23

My last resort is a long CASE statement

Thanks,
Dave

 

[HiTechCoach]

I have a field with the date/time in it, in the format:

yyyy-nn-dd hh:mm:ss:000

and example would be:

2009-1-10 23:12:00:000

Is there any way for me to just list the hour in 24 hour format?
For the above example I would need 23

My last resort is a long CASE statement

Thanks,
Dave

You format string does not math your examnpk outbut.

? now()
1/11/2009 11:13:11 PM 

? Format(date(),"yyyy-nn-dd hh:mm:ss:000")
2009-13-11 23:14:11:000

You may have meant:

? now()
1/11/2009 11:15:36 PM 

? Format(now(),"yyyy-mm-dd hh:nn:ss:000")
2009-01-11 23:15:36:000

Note: you example output has singlr digit month but your format state has it as two digits.

To get the example out that you gave you will need to use:

? now()
1/11/2009 11:15:36 PM 

? Format(now(),"yyyy-m-d hh:nn:ss:000")
2009-1-11 23:15:36:000

 

[SQL_Hell]

Here is the sql server method of doing what you ask:

[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]datepart[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]hour[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]convert[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]datetime[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]getdate[/COLOR][/SIZE][SIZE=2][COLOR=#808080](),[/COLOR][/SIZE][SIZE=2]121[/SIZE][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE]

Replace getdate() with the name of your column and use it in a select satement.