formatting times

[rkrause]

I have a sql table that has a date field but the dates are in a format like this: 90000 = 9:00:00am or 190000 = 19:00:00pm

what i want to know is how can i convert those values to normal time values and if they are military time convert those to 12 hr time formats.

my ending goal would be 90000 = 9:00:00am and 190000 = 7:00:00pm

Thanks.

 

[sunshine076]

try this
select convert(varchar, getdate(), 108)

 

[rkrause]

I have tried that but im not sure how to use that within my query? the field i wanna convert is called shipdate. any ideas?

 

[rkrause]

anyone help me on this? Im not sure where to insert my field so that it converts my specific field to the format i want...

 

[namliam]

Getdate() is the sql server funtion that returns the current time + current date.
i.e. Now() in access.

Simply replace your field for the Getdate() and you are good to go...
http://msdn.microsoft.com/en-us/library/ms187928.aspx
For more info on the convert.

However your trying to do the reverse, but... you have a number, thus 15 seconds past midnight is 15 instead of 00:00:15

In order to convert your number field into a real time, you will need something like:

Select cast(
       substring(right('000000' + ltrim(cast(YourField as varchar(6))),6),1 ,2) + ':' +
       substring(right('000000' + ltrim(cast(YourField as varchar(6))),6),3 ,2) + ':' +
       substring(right('000000' + ltrim(cast(YourField as varchar(6))),6),5 ,2) as datetime)

 

[rkrause]

This is my sql statement, and im now getting my shiptime field in a time format but i still want to get it to non military time, how can i do that?



select bol.loadnumber, bol.testname,bol.testscore,bol.ldhshipdate,
substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),1 ,2) + ':' +
substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),3 ,2) + ':' +
substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),5 ,2) as datetime,
bol.shippingtemperature,
bol.rearseal,bol.gateseal,bol.vendordomeseal,bol.trucknumber,bol.trailernumber,bol.purchaseorder,
bol.ldhticketnumber,bol.carriername,bol.ldhbilloflading,bol.shiptocode,bol.destination,bol.shiptoname,
bol.itemdesc, bt.obtccd,bt.obtnam,bt.obtabr
from dbo.v_nlp_BillOfLading_BK bol

 

[namliam]

That is NOT your sql, cause it will break....

Military time? you mean 19:00:00 I guess and you want 07:00:00 pm

Check out the link for the convert function, that will change the time into any format you can imagine.

 

[rkrause]

Yes thats the conversion i want. I dont see anything on that convert link about military time...

 

[namliam]

The source for any time is 'a real date time' value. Convert makes it into what you want it too....

Like format does in access, you do not specify what format date is comming in, it is just a date.

Because what I am doing returns 'a real datetime' because of the cast(...as datetime)
What you then need to use is something like Sunshine has shown you:
convert(varchar(30), getdate(), 9)

Replace the Getdate() by the code for 'putting together' the real date that I gave you earlier.

 

[rkrause]

Ok im getting somewhere now. but its still not what i want
my following query returns this in the shiptime field:
Jan 1 1900 3:00:00:000PM everything is correct but how can i take off the Jan 1 1900 part? I dont need that i just need time...Thanks.





select bol.loadnumber, bol.testname,bol.testscore,bol.ldhshipdate,
convert(varchar(30), Cast(
substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),1 ,2) + ':' +
substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),3 ,2) + ':' +
substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),5 ,2) as datetime), 9) as shiptime,
bol.shippingtemperature,
bol.rearseal,bol.gateseal,bol.vendordomeseal,bol.trucknumber,bol.trailernumber,bol.purchaseorder,
bol.ldhticketnumber,bol.carriername,bol.ldhbilloflading,bol.shiptocode,bol.destination,bol.shiptoname,
bol.itemdesc, bt.obtccd,bt.obtnam,bt.obtabr
from dbo.v_nlp_BillOfLading_BK bol
inner join ofbtm bt on bol.shiptocode = bt.obtccd
where bol.testname = 'BFT' and datediff(d,bol.ldhshipdate, GETDATE())= 0 or datediff(d,bol.ldhshipdate, GETDATE())= -1

 

[namliam]

SQL server doesnt have a time only format that I know of, but please double check the Convert link I gave you.

Use the Right function to take only the last X characters you want .... or use a substring function, also possible.

A few tips for your sql:

from dbo.v_nlp_BillOfLading_BK bol 
inner join ofbtm bt on bol.shiptocode = bt.obtccd
where bol.testname = 'BFT' and datediff(d,bol.ldhshipdate, GETDATE())= 0 or datediff(d,bol.ldhshipdate, GETDATE())= -1

dbo. either you use it or you dont.... or is ofbtm a local table? It looks like it but if not add dbo. infront of it.

Your where clause is both flawed and ineffective.
when your mixing AND and OR in one where clause you HAVE TO make sure it is interperted right by SQL, you do this using brackets
The current sql can be interperted in two ways, that are totaly different:

where (     bol.testname = 'BFT' 
        and datediff(d,bol.ldhshipdate, GETDATE())= 0 ) 
   or datediff(d,bol.ldhshipdate, GETDATE())= -1

and

where bol.testname = 'BFT' 
  and (     datediff(d,bol.ldhshipdate, GETDATE())= 0 
         or datediff(d,bol.ldhshipdate, GETDATE())= -1 )

Finaly your doing the same thing 2 times in your where clause, in this case not a biggy....
But in general that is not a good idea, assuming you want the second logic...

where bol.testname = 'BFT' 
  and (     datediff(d,bol.ldhshipdate, GETDATE()) in ( 0 , -1 ) )

or

where bol.testname = 'BFT' 
  and (     datediff(d,bol.ldhshipdate, GETDATE()) between -1 and 0 )

Also to keep the total query readable...

select bol.loadnumber
     , bol.testname
     , bol.testscore
     , bol.ldhshipdate
     , convert(varchar(30), Cast(
           substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),1 ,2) + ':' +
           substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),3 ,2) + ':' +
           substring(right('000000' + ltrim(cast(bol.shiptime as varchar(6))),6),5 ,2) as datetime), 9) as shiptime
     , bol.shippingtemperature
     , bol.rearseal
     , bol.gateseal
     , bol.vendordomeseal
...